package selef.xiafan.dao;

import io.lettuce.core.dynamic.annotation.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import selef.xiafan.entity.order.Settlement;

import java.util.List;

/**
 * @author xiafan
 * @version 1.0
 * @date 2021/8/1 8:00
 */
public interface SettleMentDao extends JpaRepository<Settlement, String> {
    @Query(nativeQuery = true, value = "SELECT COUNT(*) FROM t_settlement")
    Long countAll();

    Settlement findBySettleId(String settleId);

    @Query(nativeQuery = true, value = "SELECT * FROM t_settlement ORDER BY status LIMIT :page,:size")
    List<Settlement> findAllNoQuery(@Param("page") Integer page, @Param("size") Integer size);

    @Query(value = "SELECT * FROM t_settlement st LEFT JOIN t_order ot " +
            "ON st.oid = ot.order_id LEFT JOIN t_order_customer oct " +
            "ON oct.oid = ot.order_id LEFT JOIN t_customer ct " +
            "ON ct.id = oct.cid WHERE ct.name LIKE CONCAT('%',:name,'%') AND ct.car_num LIKE CONCAT('%',:carNum,'%') ORDER BY st.status LIMIT :page,:size", nativeQuery = true)
    List<Settlement> findAllWithQuery(@Param("page") Integer page, @Param("size") Integer size, @Param("name") String name,@Param("car_num")String carNum);

}
